from MyLogger import MyLogger
from MySqlConn import DBConfig, MySqlConn


def CreateTabletDataset():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    # sql = """CREATE TABLE EMPLOYEE (
    #          FIRST_NAME  CHAR(20) NOT NULL,
    #          LAST_NAME  CHAR(20),
    #          AGE INT,
    #          SEX CHAR(1),
    #          INCOME FLOAT )"""
    # dropSql = "DROP TABLE IF EXISTS EMPLOYEE"

    sql = """CREATE TABLE tDataset(
	         id int primary key auto_increment not null,
	         description varchar(400),
	         FileName char(200) not null,
	         nRows int not null,
	         nCols int not null,
	         className char(200),
	         numOfClusters int,
	         trueCentersFileName char(200) not null,
	         mixedParameters BLOB,
	         name char(200) not null) """

    # dropSql = "DROP TABLE IF EXISTS tDataset"

    ok = con.createTable(sql)
    if not ok:
        print('.....failed..create table')



def CreateTableiNiceRuns():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    # sql = """CREATE TABLE EMPLOYEE (
    #          FIRST_NAME  CHAR(20) NOT NULL,
    #          LAST_NAME  CHAR(20),
    #          AGE INT,
    #          SEX CHAR(1),
    #          INCOME FLOAT )"""
    # dropSql = "DROP TABLE IF EXISTS EMPLOYEE"

    sql = """create table tIniceRuns(
	         id int primary key auto_increment not null,
	         datasetId int not null,
	         startTime TIMESTAMP,
	         endTime TIMESTAMP,
	         ObserverGeneratorClassName char(100) not null,
	         numOfObservers int,
	         centres BLOB,
	         API double,
	         NMI double,
	         purity double,
	         name char(200) not null,
	         numOfCenters int not null,
	         foreign key(datasetId) references tDataset(id))"""

    # dropSql = "DROP TABLE IF EXISTS iNiceRuns"
    ok = con.createTable(sql)
    if not ok:
        print('.....failed..create table')


def CreateTabletObserver():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    # sql = """CREATE TABLE EMPLOYEE (
    #          FIRST_NAME  CHAR(20) NOT NULL,
    #          LAST_NAME  CHAR(20),
    #          AGE INT,
    #          SEX CHAR(1),
    #          INCOME FLOAT )"""
    # dropSql = "DROP TABLE IF EXISTS EMPLOYEE"

    sql = """create table tObserver(
	         id int primary key auto_increment not null,
	         sessionId int not null,
	         distancesFileName varchar(200),
	         clusterEstimatorClassName char(100) not null,
	         viewpoint BLOB,
	         estClusterIdsFileName varchar(200),
	         probClustersFileName char(200),
	         numOfCenters int,
	         centers BLOB,
	         One2OneDistanceClassName char(200),
	         N2NDistancesClassName char(200),
	         foreign key(sessionId) references tIniceRuns(id))"""

    dropSql = "DROP TABLE IF EXISTS tObserver"
    ok = con.createTable(sql, dropSql)
    if not ok:
        print('.....failed..create table')


def CreateTabletGammaClustersEstimatorBy1D():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    # sql = """CREATE TABLE EMPLOYEE (
    #          FIRST_NAME  CHAR(20) NOT NULL,
    #          LAST_NAME  CHAR(20),
    #          AGE INT,
    #          SEX CHAR(1),
    #          INCOME FLOAT )"""
    # dropSql = "DROP TABLE IF EXISTS EMPLOYEE"

    sql = """create table tGammaClustersEstimatorBy1D(
	         id int primary key auto_increment not null,
	         observerId int not null,
	         epsilon double not null,
	         maxIter int not null,
	         maxRestarts int not null,
	         verb TINYINT(1) not null,
	         ShapeList BLOB,
	         ScaleList BLOB,
	         PaiList BLOB,
	         numOfComponents int not null,
	         negLogLikelihood double,
	         regItem double,
	         foreign key(observerId) references tObserver(id));

"""

    dropSql = "DROP TABLE IF EXISTS tGammaClustersEstimatorBy1D"
    ok = con.createTable(sql, dropSql)
    if not ok:
        print('.....failed..create table')


def CreateTableHighDensityFilter():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    # sql = """CREATE TABLE EMPLOYEE (
    #          FIRST_NAME  CHAR(20) NOT NULL,
    #          LAST_NAME  CHAR(20),
    #          AGE INT,
    #          SEX CHAR(1),
    #          INCOME FLOAT )"""
    # dropSql = "DROP TABLE IF EXISTS EMPLOYEE"

    sql = """create table tHighDensityFilter(
	         id int primary key auto_increment not null,
	         clusterEstimatorId int not null,
	         outputIds BLOB,
        	 filterClassName varchar(200),
	         fromFilterId int not null,
	         whichComponent int not null,
	         foreign key(clusterEstimatorId) references tGammaClustersEstimatorBy1D(id),
	         foreign key(fromFilterId) references HighDensityFilter(id));"""

    dropSql = "DROP TABLE IF EXISTS HighDensityFilter"
    ok = con.createTable(sql, dropSql)
    if not ok:
        print('.....failed..create table')

def insertiNiceRuns():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    sql = """INSERT INTO tIniceRuns(id, datasetId, 
           ObserverGeneratorClassName, numOfObservers, centres, name, numOfCenters) 
                   VALUES (1, 1, 'Edge', 3, 'test', 'config_1', 5)"""

    ok = con.insDelUpd(sql)
    if not ok:
        print('.....failed...insert')

    sql = """INSERT INTO tIniceRuns(id, datasetId, 
                   ObserverGeneratorClassName, numOfObservers, centres, name, numOfCenters) 
                           VALUES (2, 2, 'Simple', 4, 'test3', 'config_2', 6)"""
    ok = con.insDelUpd(sql)
    if not ok:
        print('.....failed...insert')


def insert_tDataset():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    sql = """INSERT INTO tDataset(id, description, FileName, nRows, nCols, className, trueCentersFileName, 
            mixedParameters, numOfClusters, name) VALUES (1, 'students', 'students.csv', 100, 3, 'gamma', 'center.csv', 
            'mix', '6', 'stu')"""

    ok = con.insDelUpd(sql)
    if not ok:
        print('.....failed...insert')

    sql = """INSERT INTO tDataset(id, description, FileName, nRows, nCols, className, trueCentersFileName, 
                    mixedParameters, numOfClusters, name) VALUES (2, 'students', 'students.csv', 100, 3, 'gamma', 'center.csv', 
                    'mix', '6', 'stu')"""

    ok = con.insDelUpd(sql)
    if not ok:
        print('.....failed...insert')

def insert_tObserver():
    logger = MyLogger('test')
    conf = DBConfig()

    con = MySqlConn(conf, logger)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    sql0 = """INSERT INTO tObserver(id, sessionId, clusterEstimatorClassName, One2OneDistanceClassName, N2NDistancesClassName) 
                   VALUES (1, 1, 'GammaClustersEstimatorBy1D', 'Euclidean Distance', 'Gaussian Distance')"""

    sql1 = """INSERT INTO tObserver(id, sessionId, clusterEstimatorClassName, One2OneDistanceClassName, N2NDistancesClassName) 
                   VALUES (2, 1, 'GaussianClustersEstimatorBy1D', 'Gaussian Distance', 'Gaussian Distance')"""

    sql2 = """INSERT INTO tObserver(id, sessionId, clusterEstimatorClassName, One2OneDistanceClassName, N2NDistancesClassName) 
                   VALUES (3, 1, 'GaussianClustersEstimatorBy1D', 'Euclidean Distance', 'Euclidean Distance')"""

    if con.insDelUpd(sql0) and con.insDelUpd(sql1) and con.insDelUpd(sql2):
        print('...insert')

    sql3 = """INSERT INTO tObserver(id, sessionId, clusterEstimatorClassName, One2OneDistanceClassName, N2NDistancesClassName) 
                       VALUES (4, 2, 'GammaClustersEstimatorBy1D', 'Euclidean Distance', 'Gaussian Distance')"""

    sql4 = """INSERT INTO tObserver(id, sessionId, clusterEstimatorClassName, One2OneDistanceClassName, N2NDistancesClassName) 
                       VALUES (5, 2, 'GaussianClustersEstimatorBy1D', 'Gaussian Distance', 'Gaussian Distance')"""

    sql5 = """INSERT INTO tObserver(id, sessionId, clusterEstimatorClassName, One2OneDistanceClassName, N2NDistancesClassName) 
                       VALUES (6, 2, 'GaussianClustersEstimatorBy1D', 'Euclidean Distance', 'Euclidean Distance')"""

    sql6 = """INSERT INTO tObserver(id, sessionId, clusterEstimatorClassName, One2OneDistanceClassName, N2NDistancesClassName) 
                           VALUES (7, 2, 'GammaClustersEstimatorBy1D', 'Euclidean Distance', 'Euclidean Distance')"""

    if con.insDelUpd(sql3) and con.insDelUpd(sql4) and con.insDelUpd(sql5) and con.insDelUpd(sql6):
        print('...insert')


if __name__ == '__main__':
    # CreateTabletDataset()
    # CreateTableiNiceRuns()
    #
    # insert_tDataset()
    # insertiNiceRuns()
    CreateTabletObserver()
    insert_tObserver()


